﻿/*
 * Erstellt mit SharpDevelop.
 * Benutzer: Christian
 * Datum: 18.05.2010
 * Zeit: 17:49
 * 
 * Sie können diese Vorlage unter Extras > Optionen > Codeerstellung > Standardheader ändern.
 */
using System;
using System.Collections.Generic;
using System.IO;
using System.Windows.Forms;

using Finisar.SQLite;

namespace MovieExplorer
{
   /// <summary>
   /// Description of MovieInformation.
   /// </summary>
   public class MovieInformation
   {
      public string title  = ""; 
      public string path  = ""; 
      public string filename  = ""; 
      public string releasedate  = ""; 
      public string Categories  = ""; 
      public string Description  = ""; 
      public string People_Actors  = ""; 
      public string People_Director  = ""; 
      public string People_Casting  = ""; 
      public string People_Photography  = ""; 
      public string People_Editor  = ""; 
      public string People_Composer  = ""; 
      public string People_Producer  = ""; 
      public string People_Author  = ""; 
      public string People_novel  = ""; 
      public string People_screenplay  = "";
      public string People_CoProducer  = ""; 
      public string People_ExecutiveProducer  = ""; 
      public string People_set_decoration  = ""; 
      public string People_production_design  = ""; 
      public string ProductionCountry  = ""; 
      public string Trailer  = ""; 
      public string IMDB  = ""; 
      public string Folder  = ""; 
      public string Backdrop  = ""; 
      public string Rating  = ""; 
      public string Runtime_Original  = ""; 
      public string Budget  = ""; 
      public string Revenue  = "";
      public string AlternativeTitles  = ""; 
      public string TheMovieDbId  = ""; 
      public string filedate  = ""; 
      public string videocodec  = ""; 
      public string videobitrate  = ""; 
      public string videowidth  = "";
      public string videoheight  = ""; 
      public string videoframerate  = ""; 
      public string audiocodec  = ""; 
      public string audiobitrate  = "";
      public string audiochannels  = "";

      public MovieInformation()
      {

      }

      public void WriteInfoFile(string dir)
      {
         StreamWriter sw = new StreamWriter(dir + "info.txt");

         sw.WriteLine("Titel: " + this.title);
         sw.WriteLine("FilmID: " + this.TheMovieDbId);
         sw.WriteLine("imdb: " + this.IMDB);
         sw.WriteLine("Link: {http://www.themoviedb.org/movie/" + this.TheMovieDbId + "/}");
         sw.WriteLine("Inhalt: " + this.Description);
         sw.WriteLine("Bewertung: " + this.Rating);
         sw.WriteLine("Runtime_Original: " + this.Runtime_Original);
         sw.WriteLine("Budget: " + this.Budget);
         sw.WriteLine("Categories: " + this.Categories);

         sw.WriteLine("People_actor: " + this.People_Actors);
         sw.WriteLine("People_adr_mixer: ");
         sw.WriteLine("People_art_direction: ");
         sw.WriteLine("People_assistent_sound_designer: ");
         sw.WriteLine("People_casting: " + this.People_Casting);
         sw.WriteLine("People_co-producer: " + this.People_CoProducer);
         sw.WriteLine("People_costume_design: ");
         sw.WriteLine("People_director: " + this.People_Director);
         sw.WriteLine("People_director_of_photography: ");
         sw.WriteLine("People_editor: " + this.People_Editor);
         sw.WriteLine("People_executive_producer: " + this.People_ExecutiveProducer);
         sw.WriteLine("People_makeup_artist: ");
         sw.WriteLine("People_music_editor: ");
         sw.WriteLine("People_novel: " + this.People_novel);
         sw.WriteLine("People_original_music_composer: " + this);
         sw.WriteLine("People_producer: " + this.People_Producer);
         sw.WriteLine("People_production_design: " + this.People_production_design);
         sw.WriteLine("People_screenplay: " + this.People_screenplay);
         sw.WriteLine("People_set_decoration: " + this.People_set_decoration);
         sw.WriteLine("People_sound_editor: ");
         sw.WriteLine("Production_Countries: " + this.ProductionCountry);

         sw.Close();
      }

      public void AddToDB(string configdir, string dbfile, bool bV3, bool bUTF8)
      {
         string dataSource = configdir + dbfile + ".db";
         if (bV3)
         {
            dataSource = configdir + dbfile + ".db3;Version=3";
         }
         if(bUTF8)
         {
            dataSource += ";UTF8Encoding=True";
         }

         SQLiteConnection connection = new SQLiteConnection();

         connection.ConnectionString = "Data Source=" + dataSource;
         connection.Open();


         SQLiteCommand command = connection.CreateCommand();

         // Erstellen der Tabelle, sofern diese noch nicht existiert.
         //command.CommandText = "CREATE TABLE IF NOT EXISTS beispiel ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name VARCHAR(100) NOT NULL);";
         //command.ExecuteNonQuery();

         command.CommandText = "SELECT MAX(id) FROM movies";
         command.ExecuteNonQuery();

         SQLiteDataReader reader = command.ExecuteReader();

         int idmax = 0;
         if (reader.Read())
         {
            int.TryParse(reader[0].ToString(), out idmax);
         }
         idmax++;

         // Beenden des Readers und Freigabe aller Ressourcen.
         reader.Dispose();
         reader.Close();

         // Freigabe der Ressourcen.
         command.Dispose();


         command = connection.CreateCommand();

         // Erstellen der Tabelle, sofern diese noch nicht existiert.
         //command.CommandText = "CREATE TABLE IF NOT EXISTS beispiel ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name VARCHAR(100) NOT NULL);";
         //command.ExecuteNonQuery();

         command.CommandText = "INSERT INTO movies (id, title, path, filename, releasedate, Categories, Description, People_Actors, People_Director, People_Casting, People_Photography, People_Editor, People_Composer, People_Producer, People_Author, People_novel, People_screenplay, People_CoProducer, People_ExecutiveProducer, People_set_decoration, People_production_design, ProductionCountry, Trailer, IMDB, Folder, Backdrop, Rating, Runtime_Original, Budget, Revenue, AlternativeTitles, TheMovieDbId, filedate, videocodec, videobitrate, videowidth, videoheight, videoframerate, audiocodec, audiobitrate, audiochannels) ";
         command.CommandText += string.Format("VALUES (\"{0}\", \"{1}\", \"{2}\", \"{3}\", \"{4}\", \"{5}\", \"{6}\", \"{7}\", \"{8}\", \"{9}\", \"{10}\", \"{11}\", \"{12}\", \"{13}\", \"{14}\", \"{15}\", \"{16}\", \"{17}\", \"{18}\", \"{19}\", \"{20}\", \"{21}\", \"{22}\", \"{23}\", \"{24}\", '{25}', \"{26}\", \"{27}\", \"{28}\", \"{29}\", \"{30}\", \"{31}\", \"{32}\", \"{33}\", \"{34}\", \"{35}\", \"{36}\", \"{37}\", \"{38}\", \"{39}\", \"{40}\")",
                                              idmax, title, path, filename, releasedate, Categories, Description, People_Actors, People_Director, People_Casting, People_Photography, People_Editor, People_Composer, People_Producer, People_Author, People_novel, People_screenplay, People_CoProducer, People_ExecutiveProducer, People_set_decoration, People_production_design, ProductionCountry, Trailer, IMDB, Folder, Backdrop, Rating, Runtime_Original, Budget, Revenue, AlternativeTitles, TheMovieDbId, filedate, videocodec, videobitrate, videowidth, videoheight, videoframerate, audiocodec, audiobitrate, audiochannels);

         command.ExecuteNonQuery();

         // Freigabe der Ressourcen.
         command.Dispose();

         connection.Dispose();
         connection.Close();
      }

      public static MovieInformation GetFromDB(int id, string configdir, bool bV3, bool bUTF8)
      {
         MovieInformation result = new MovieInformation();
         
         string dataSource = configdir + "mymovies.db";
         if (bV3)
         {
            dataSource = configdir + "mymovies.db3;Version=3";
         }
         if(bUTF8)
         {
            dataSource += ";UTF8Encoding=True";
         }

         SQLiteConnection connection = new SQLiteConnection();

         connection.ConnectionString = "Data Source=" + dataSource;
         connection.Open();


         SQLiteCommand command = connection.CreateCommand();

         command.CommandText = "SELECT title, path, filename, releasedate, Categories, Description, People_Actors, People_Director, People_Casting, People_Photography, People_Editor, People_Composer, People_Producer, People_Author, People_novel, People_screenplay, People_CoProducer, People_ExecutiveProducer, People_set_decoration, People_production_design, ProductionCountry, Trailer, IMDB, Folder, Backdrop, Rating, Runtime_Original, Budget, Revenue, AlternativeTitles, TheMovieDbId, filedate, videocodec, videobitrate, videowidth, videoheight, videoframerate, audiocodec, audiobitrate, audiochannels FROM movies WHERE id = \"" + id.ToString() +  "\"";
         command.ExecuteNonQuery();

         SQLiteDataReader reader = command.ExecuteReader();

         if (reader.Read())
         {
            result.title = reader[0].ToString();
            result.path = reader[1].ToString();
            result.filename = reader[2].ToString();
            result.releasedate = reader[3].ToString();
            result.Categories = reader[4].ToString();
            result.Description = reader[5].ToString();
            result.People_Actors = reader[6].ToString(); 
            result.People_Director = reader[7].ToString(); 
            result.People_Casting = reader[8].ToString(); 
            result.People_Photography = reader[9].ToString(); 
            result.People_Editor = reader[10].ToString(); 
            result.People_Composer = reader[11].ToString(); 
            result.People_Producer = reader[12].ToString(); 
            result.People_Author = reader[13].ToString(); 
            result.People_novel = reader[14].ToString(); 
            result.People_screenplay = reader[15].ToString(); 
            result.People_CoProducer = reader[16].ToString(); 
            result.People_ExecutiveProducer = reader[17].ToString(); 
            result.People_set_decoration = reader[18].ToString(); 
            result.People_production_design = reader[19].ToString(); 
            result.ProductionCountry = reader[20].ToString(); 
            result.Trailer = reader[21].ToString(); 
            result.IMDB = reader[22].ToString(); 
            result.Folder = reader[23].ToString(); 
            result.Backdrop = reader[24].ToString(); 
            result.Rating = reader[25].ToString(); 
            result.Runtime_Original = reader[26].ToString(); 
            result.Budget = reader[27].ToString(); 
            result.Revenue = reader[28].ToString(); 
            result.AlternativeTitles = reader[29].ToString(); 
            result.TheMovieDbId = reader[30].ToString(); 
            result.filedate = reader[31].ToString(); 
            result.videocodec = reader[32].ToString(); 
            result.videobitrate = reader[33].ToString(); 
            result.videowidth = reader[34].ToString(); 
            result.videoheight = reader[35].ToString(); 
            result.videoframerate = reader[36].ToString(); 
            result.audiocodec = reader[37].ToString(); 
            result.audiobitrate = reader[38].ToString(); 
            result.audiochannels = reader[39].ToString();
         }

         // Beenden des Readers und Freigabe aller Ressourcen.
         reader.Dispose();
         reader.Close();
         
         // Freigabe der Ressourcen.
         command.Dispose();

         connection.Dispose();
         connection.Close();
         
         return result;
      }

      public void UpdateDB(int id, string configdir, string dbfile, bool bV3, bool bUTF8)
      {
         string dataSource = configdir + dbfile +".db";
         if (bV3)
         {
            dataSource = configdir + dbfile + ".db3;Version=3";
         }
         if(bUTF8)
         {
            dataSource += ";UTF8Encoding=True";
         }
         
         SQLiteConnection connection = new SQLiteConnection();

         connection.ConnectionString = "Data Source=" + dataSource;
         connection.Open();


         SQLiteCommand command = connection.CreateCommand();

         Backdrop = Backdrop.Replace("'", "\\'");
         command.CommandText = string.Format("UPDATE movies SET title=\"{0}\", path=\"{1}\", filename=\"{2}\", releasedate=\"{3}\", Categories=\"{4}\", Description=\"{5}\", People_Actors=\"{6}\", People_Director=\"{7}\", People_Casting=\"{8}\", People_Photography=\"{9}\", People_Editor=\"{10}\", People_Composer=\"{11}\", People_Producer=\"{12}\", People_Author=\"{13}\", People_novel=\"{14}\", People_screenplay=\"{15}\", People_CoProducer=\"{16}\", People_ExecutiveProducer=\"{17}\", People_set_decoration=\"{18}\", People_production_design=\"{19}\", ProductionCountry=\"{20}\", Trailer=\"{21}\", IMDB=\"{22}\", Folder=\"{23}\", Backdrop='{24}', Rating=\"{25}\", Runtime_Original=\"{26}\", Budget=\"{27}\", Revenue=\"{28}\", AlternativeTitles=\"{29}\", TheMovieDbId=\"{30}\", filedate=\"{31}\", videocodec=\"{32}\", videobitrate=\"{33}\", videowidth=\"{34}\", videoheight=\"{35}\", videoframerate=\"{36}\", audiocodec=\"{37}\", audiobitrate=\"{38}\", audiochannels=\"{39}\" WHERE id=\"" + id + "\"",
                                              title, path, filename, releasedate, Categories, Description, People_Actors, People_Director, People_Casting, People_Photography, People_Editor, People_Composer, People_Producer, People_Author, People_novel, People_screenplay, People_CoProducer, People_ExecutiveProducer, People_set_decoration, People_production_design, ProductionCountry, Trailer, IMDB, Folder, Backdrop, Rating, Runtime_Original, Budget, Revenue, AlternativeTitles, TheMovieDbId, filedate, videocodec, videobitrate, videowidth, videoheight, videoframerate, audiocodec, audiobitrate, audiochannels);
                     
         command.ExecuteNonQuery();

         // Freigabe der Ressourcen.
         command.Dispose();

         connection.Dispose();
         connection.Close();
      }

      public static void UpdateItemDB(int id, string itemname, string itemvalue, string configdir, string dbfile, bool bV3, bool bUTF8)
      {
         string dataSource = configdir + dbfile + ".db";
         if (bV3)
         {
            dataSource = configdir + dbfile + ".db3;Version=3";
         }
         if(bUTF8)
         {
            dataSource += ";UTF8Encoding=True";
         }
         
         SQLiteConnection connection = new SQLiteConnection();

         connection.ConnectionString = "Data Source=" + dataSource;
         connection.Open();


         SQLiteCommand command = connection.CreateCommand();

         command.CommandText = string.Format("UPDATE movies SET {0}=\"{1}\" WHERE id=\"" + id + "\"",
                                              itemname, itemvalue);
         command.ExecuteNonQuery();

         // Freigabe der Ressourcen.
         command.Dispose();

         connection.Dispose();
         connection.Close();
      }

      public static List<Movie> GetMoviesFromDB(string configdir, string dbfile, bool bV3, bool bUTF8)
      {
         List<Movie> movieList = new List<Movie>();

         string dataSource = configdir + dbfile + ".db";
         if (bV3)
         {
            dataSource = configdir + dbfile + ".db3;Version=3";
         }
         if(bUTF8)
         {
            dataSource += ";UTF8Encoding=True";
         }
         
         SQLiteConnection connection = new SQLiteConnection();

         connection.ConnectionString = "Data Source=" + dataSource;
         connection.Open();

         SQLiteCommand command = connection.CreateCommand();

         // Auslesen des zuletzt eingefügten Datensatzes.
         command.CommandText = "SELECT id, title, path, filename FROM movies";

         SQLiteDataReader reader = command.ExecuteReader();

         while (reader.Read())
         {
            Movie movie = new Movie();
            int id = 0;
            int.TryParse(reader[0].ToString(), out id);
            
            movie.ID = id;
            movie.Title = reader[1].ToString();
            movie.Path = reader[2].ToString();
            movie.Filename = reader[3].ToString();

            movieList.Add(movie);
         }

         // Beenden des Readers und Freigabe aller Ressourcen.
         reader.Dispose();
         reader.Close();

         command.Dispose();

         connection.Dispose();
         connection.Close();

         return movieList;
      }

      public static void RemoveMovieFromDB(int movieID, string configdir, string dbfile, bool bV3, bool bUTF8)
      {
         string dataSource = configdir + dbfile + ".db";
         if (bV3)
         {
            dataSource = configdir + dbfile + ".db3;Version=3";
         }
         if(bUTF8)
         {
            dataSource += ";UTF8Encoding=True";
         }
         
         SQLiteConnection connection = new SQLiteConnection();

         connection.ConnectionString = "Data Source=" + dataSource;
         connection.Open();

         SQLiteCommand command = connection.CreateCommand();

         // Auslesen des zuletzt eingefügten Datensatzes.
         command.CommandText = "DELETE FROM movies WHERE id = \"" + movieID.ToString() + "\"";
         command.ExecuteNonQuery();

         command.Dispose();

         connection.Dispose();
         connection.Close();
      }

      public static List<Recording> GetRecordings(string configdir, string dbfile, bool bV3, bool bUTF8)
      {
         List<Recording> recordingList = new List<Recording>();

         string dataSource = configdir + dbfile +".db";
         if (bV3)
         {
            dataSource = configdir + dbfile + ".db3;Version=3";
         }
         if(bUTF8)
         {
            dataSource += ";UTF8Encoding=True";
         }
         
         SQLiteConnection connection = new SQLiteConnection();

         connection.ConnectionString = "Data Source=" + dataSource;
         connection.Open();

         SQLiteCommand command = connection.CreateCommand();

         // Auslesen des zuletzt eingefügten Datensatzes.
         command.CommandText = "SELECT idRecord, Filename, channel, Title, Description FROM recordings";

         SQLiteDataReader reader = command.ExecuteReader();

         while (reader.Read())
         {
            Recording rec = new Recording();
            rec.ID = int.Parse(reader[0].ToString());
            rec.Filename = reader[1].ToString();
            rec.Channel = reader[2].ToString(); 
            rec.Title = reader[3].ToString(); 
            rec.Description = reader[4].ToString();

            recordingList.Add(rec);
         }

         // Beenden des Readers und Freigabe aller Ressourcen.
         reader.Dispose();
         reader.Close();

         command.Dispose();

         connection.Dispose();
         connection.Close();

         return recordingList;
      }

      public static void RemoveRecordFromDB(int recordID, string configdir, string dbfile, bool bV3, bool bUTF8)
      {
         string dataSource = configdir + dbfile + ".db";
         if (bV3)
         {
            dataSource = configdir + dbfile + ".db3;Version=3";
         }
         if(bUTF8)
         {
            dataSource += ";UTF8Encoding=True";
         }
         
         SQLiteConnection connection = new SQLiteConnection();

         connection.ConnectionString = "Data Source=" + dataSource;
         connection.Open();

         SQLiteCommand command = connection.CreateCommand();

         // Auslesen des zuletzt eingefügten Datensatzes.
         command.CommandText = "DELETE FROM recordings WHERE idRecord = " + recordID.ToString();
         command.ExecuteNonQuery();

         command.Dispose();

         connection.Dispose();
         connection.Close();
      }
   }
}
